SET DATEFORMAT DMY

DECLARE @FirstDate DATETIME,
        @SecondDate DATETIME,
        @dopUnn smallint

SET @FirstDate = CAST('01.01.2020' AS DATETIME)
SET @SecondDate = CAST('31.01.2020' AS DATETIME)

SELECT @dopUnn=dopUNN FROM tbPredpr WHERE idPredpr=(SELECT idPredpr FROM tbNastrPredpr) 

/*

SELECT prov.UNN+'-'+CAST(inv.compilingYear AS VARCHAR)+'-'+RIGHT('0000000000'+CAST(inv.number AS VARCHAR),10) AS Fullname, his.invStatus, ISNULL(doc.docNumber, spr.NumDoc) AS docNumber, 
       ISNULL(doc.docDate, spr.DataDoc) AS docDate, spr.Summ, ISNULL(inv.totalVat, 0) as totalVat, ISNULL(inv.totalCost, 0) AS totalCost, @dopUnn
FROM invoices inv
     INNER JOIN history his ON inv.idSender=his.idSender AND inv.compilingYear=his.compilingYear AND inv.number=his.number
     LEFT JOIN documents doc ON inv.idSender=doc.idSender AND inv.compilingYear=doc.compilingYear AND inv.number=doc.number
     INNER JOIN tbPredpr prov ON prov.idPredpr=inv.idProvider
     FULL JOIN (SELECT spr.NSF, SUM(spr.Summ) AS Summ, spr.DataDoc, spr.NumDoc 
                FROM buh_o.dbo.tbSpravka spr
                     INNER JOIN buh_o.dbo.tbSFProvNDS provDT ON spr.idSchetSubSchetDT=provDT.IdDt AND spr.PrizAUDT=provDT.AuDt
                     INNER JOIN buh_o.dbo.tbSFProvNDS provKT ON spr.idSchetSubSchetKT=provKT.IdKt AND spr.PrizAUKT=provKT.AuKt
                WHERE (DataDoc BETWEEN @FirstDate and @SecondDate) AND PrizRej=1
                GROUP BY spr.NSF, spr.DataDoc, spr.NumDoc) spr ON prov.UNN+'-'+CAST(inv.compilingYear AS VARCHAR)+'-'+RIGHT('0000000000'+CAST(inv.number AS VARCHAR),10)=Substring(spr.NSF,3,25)
WHERE inv.sv=1 and ((inv.dateTransaction BETWEEN @FirstDate and @SecondDate) or (doc.docDate BETWEEN @FirstDate and @SecondDate)) 

UNION 

SELECT inv.Fullname, inv.invStatus, ISNULL(inv.docNumber, spr.NumDoc) AS docNumber, 
       ISNULL(inv.docDate, spr.DataDoc) AS docDate, SUM(spr.Summ) AS sprSum, inv.totalVat, inv.totalCost, @dopUnn
FROM buh_o.dbo.tbSpravka spr
     INNER JOIN buh_o.dbo.tbSFProvNDS provDT ON spr.idSchetSubSchetDT=provDT.IdDt AND spr.PrizAUDT=provDT.AuDt
     INNER JOIN buh_o.dbo.tbSFProvNDS provKT ON spr.idSchetSubSchetKT=provKT.IdKt AND spr.PrizAUKT=provKT.AuKt
     FULL JOIN (SELECT prov.UNN+'-'+CAST(inv.compilingYear AS VARCHAR)+'-'+RIGHT('0000000000'+CAST(inv.number AS VARCHAR),10) AS Fullname, his.invStatus, doc.docNumber, 
                       doc.docDate, ISNULL(inv.totalVat, 0) as totalVat, ISNULL(inv.totalCost, 0) AS totalCost
		FROM invoices inv
		     INNER JOIN history his ON inv.idSender=his.idSender AND inv.compilingYear=his.compilingYear AND inv.number=his.number
		     LEFT JOIN documents doc ON inv.idSender=doc.idSender AND inv.compilingYear=doc.compilingYear AND inv.number=doc.number
		     INNER JOIN tbPredpr prov ON prov.idPredpr=inv.idProvider
		WHERE inv.sv=1 and ((inv.dateTransaction BETWEEN @FirstDate and @SecondDate) or (doc.docDate BETWEEN @FirstDate and @SecondDate))
               ) inv ON inv.fullName=Substring(spr.NSF,3,25)
WHERE (DataDoc BETWEEN @FirstDate and @SecondDate) AND PrizRej=1
GROUP BY inv.Fullname, inv.invStatus, ISNULL(inv.docNumber, spr.NumDoc), ISNULL(inv.docDate, spr.DataDoc), inv.totalVat, inv.totalCost
       
ORDER BY inv.Fullname

---------------------------------------------

SELECT prov.UNN+'-'+CAST(inv.compilingYear AS VARCHAR)+'-'+RIGHT('0000000000'+CAST(inv.number AS VARCHAR),10) AS Fullname, 
             his.invStatus, doc.docNumber, doc.docDate, inv.totalVat, inv.totalCost
      FROM invoices inv
           INNER JOIN history his ON inv.idSender=his.idSender AND inv.compilingYear=his.compilingYear AND inv.number=his.number
           LEFT JOIN documents doc ON inv.idSender=doc.idSender AND inv.compilingYear=doc.compilingYear AND inv.number=doc.number
           INNER JOIN tbPredpr prov ON prov.idPredpr=inv.idProvider
      WHERE inv.sv=1 and ((inv.dateTransaction BETWEEN @FirstDate and @SecondDate) or (doc.docDate BETWEEN @FirstDate and @SecondDate))

----------------------------------------------

SELECT prov.UNN+'-'+CAST(inv.compilingYear AS VARCHAR)+'-'+RIGHT('0000000000'+CAST(inv.number AS VARCHAR),10) AS Fullname, 
             his.invStatus, doc.docNumber, doc.docDate, inv.totalVat, inv.totalCost
      FROM invoices inv
           INNER JOIN history his ON inv.idSender=his.idSender AND inv.compilingYear=his.compilingYear AND inv.number=his.number
           LEFT JOIN documents doc ON inv.idSender=doc.idSender AND inv.compilingYear=doc.compilingYear AND inv.number=doc.number
           INNER JOIN tbPredpr prov ON prov.idPredpr=inv.idProvider
      WHERE inv.sv=1 and documentType in (1, 4) and ((inv.dateTransaction BETWEEN @FirstDate and @SecondDate) or (doc.docDate BETWEEN @FirstDate and @SecondDate)) 

UNION

SELECT prov.UNN+'-'+CAST(inv.compilingYear AS VARCHAR)+'-'+RIGHT('0000000000'+CAST(inv.number AS VARCHAR),10) AS Fullname, 
             his.invStatus, doc.docNumber, doc.docDate, inv.totalVat, inv.totalCost--, invorg.number
FROM invoices inv
      INNER JOIN history his ON inv.idSender=his.idSender AND inv.compilingYear=his.compilingYear AND inv.number=his.number
           LEFT JOIN documents doc ON inv.idSender=doc.idSender AND inv.compilingYear=doc.compilingYear AND inv.number=doc.number
           INNER JOIN tbPredpr prov ON prov.idPredpr=inv.idProvider
           INNER JOIN invoices invorg ON invorg.idSender=inv.idSender and CAST(Substring(inv.invoice, 11, 4) AS smallint)=invorg.compilingYear and CAST(Substring(inv.invoice, 16, 10) AS bigint)=invorg.number
           INNER JOIN documents docorg ON invorg.idSender=docorg.idSender AND invorg.compilingYear=docorg.compilingYear AND invorg.number=docorg.number
      WHERE inv.sv=1 and inv.documentType in (2, 3) and ((inv.dateTransaction BETWEEN @FirstDate and @SecondDate) or (doc.docDate BETWEEN @FirstDate and @SecondDate) or (invorg.dateTransaction BETWEEN @FirstDate and @SecondDate) or (docorg.docDate BETWEEN @FirstDate and @SecondDate))  
*/

if object_id('tempdb..#inv_prov','U') is not NULL
drop table tempdb..#inv_prov

CREATE TABLE tempdb..#inv_prov(
fullName VARCHAR(30),
documentType tinyint,
invStatus smallint,
dateTrans DATETIME,
docNumber VARCHAR(30),
docDate DATETIME,
sprSum NUMERIC(18, 3),
totalVat DECIMAL(18, 3),
totalCost DECIMAL(18, 3),
totalCostVat DECIMAL(18, 3),
dopUnn smallint,
NumBuhSpr VARCHAR(10),
idPredpr int,
globalcode bigint
)

INSERT INTO #inv_prov(fullName, documentType, invStatus, dateTrans, docNumber, docDate, sprSum, totalVat, totalCost, totalCostVat, dopUnn, NumBuhSpr, idPredpr, globalcode)
SELECT inv.Fullname, inv.documentType, inv.invStatus, inv.dateTransaction, ISNULL(inv.docNumber, spr.NumDoc) AS docNumber, 
       ISNULL(inv.docDate, spr.DataDoc) AS docDate, spr.Summ, inv.totalVat, inv.totalCost, inv.totalCostVat, @dopUnn as dopUnn, /*spr.NSF, Substring(spr.NSF,3,25),*/ 
       spr.NumBuhSpr, ISNULL(spr.idPredpr, inv.idSender), ISNULL(spr.globalcode, inv.globalcode)
FROM (-- Исходные и Дополнительные без ссылки ЭСЧФ 
      SELECT prov.UNN+'-'+CAST(inv.compilingYear AS VARCHAR)+'-'+RIGHT('0000000000'+CAST(inv.number AS VARCHAR),10) AS Fullname, 
             his.invStatus, doc.docNumber, doc.docDate, inv.totalVat, inv.totalCost, inv.totalCostVat, inv.documentType, inv.idSender, prov.globalcode, inv.dateTransaction
      FROM invoices inv
           INNER JOIN history his ON inv.idSender=his.idSender AND inv.compilingYear=his.compilingYear AND inv.number=his.number
           LEFT JOIN documents doc ON inv.idSender=doc.idSender AND inv.compilingYear=doc.compilingYear AND inv.number=doc.number
           INNER JOIN tbPredpr prov ON prov.idPredpr=inv.idProvider
      WHERE inv.sv=1 and documentType in (1, 4) and ((inv.dateTransaction BETWEEN @FirstDate and @SecondDate) or (doc.docDate BETWEEN @FirstDate and @SecondDate)) 

      UNION
      -- Дополнительные и Исправлеыыне ЭСЧФ (дополнительно проверяется дата оригинальной ЭСЧФ)
      SELECT prov.UNN+'-'+CAST(inv.compilingYear AS VARCHAR)+'-'+RIGHT('0000000000'+CAST(inv.number AS VARCHAR),10) AS Fullname,
             his.invStatus, doc.docNumber, doc.docDate, inv.totalVat, inv.totalCost, inv.totalCostVat, inv.documentType, inv.idSender, prov.globalcode, inv.dateTransaction
      FROM invoices inv
           INNER JOIN history his ON inv.idSender=his.idSender AND inv.compilingYear=his.compilingYear AND inv.number=his.number
           LEFT JOIN documents doc ON inv.idSender=doc.idSender AND inv.compilingYear=doc.compilingYear AND inv.number=doc.number
           INNER JOIN tbPredpr prov ON prov.idPredpr=inv.idProvider
           LEFT JOIN invoices invorg ON invorg.idSender=inv.idSender and CAST(Substring(inv.invoice, 11, 4) AS smallint)=invorg.compilingYear and CAST(Substring(inv.invoice, 16, 10) AS bigint)=invorg.number
           LEFT JOIN documents docorg ON invorg.idSender=docorg.idSender AND invorg.compilingYear=docorg.compilingYear AND invorg.number=docorg.number
      WHERE inv.sv=1 and inv.documentType in (2, 3) and 
           ((inv.dateTransaction BETWEEN @FirstDate and @SecondDate) or (doc.docDate BETWEEN @FirstDate and @SecondDate) or 
            (invorg.dateTransaction BETWEEN @FirstDate and @SecondDate) or (docorg.docDate BETWEEN @FirstDate and @SecondDate))) inv               
      FULL JOIN (-- Выборка сумм из справки по проводкам
                 SELECT spr.NSF, SUM(spr.Summ) AS Summ, spr.DataDoc, spr.NumDoc, spr.NumBuhSpr, spr.idPredpr, pred.globalcode
                 FROM buh_o.dbo.tbSpravka spr
                      INNER JOIN buh_o.dbo.tbSFProvNDS provDT ON spr.idSchetSubSchetDT=provDT.IdDt AND spr.PrizAUDT=provDT.AuDt
                      INNER JOIN buh_o.dbo.tbSFProvNDS provKT ON spr.idSchetSubSchetKT=provKT.IdKt AND spr.PrizAUKT=provKT.AuKt
                      LEFT JOIN tbPredpr pred ON spr.idPredpr=pred.idPredpr
                 WHERE (DataDoc BETWEEN @FirstDate and @SecondDate) AND PrizRej=1 AND Sv=0
                 GROUP BY spr.NSF, spr.DataDoc, spr.NumDoc, spr.NumBuhSpr, spr.idPredpr, pred.globalcode) spr ON (inv.Fullname=Substring(spr.NSF,3,25)) 
--WHERE spr.Summ is NULL or totalVat is Null 
ORDER BY inv.Fullname

/*
SELECT * 
FROM #minsk_prov
WHERE fullName is NULL 
*/

-- Поиск дубликатов проводок
SELECT main.NumBuhSpr, main.docNumber, main.docDate, main.sprSum, esf.numBuhSpr, esf.fullName, esf.totalVat, pr.namePredpr, main.dopUNN
FROM #inv_prov main
     INNER JOIN (SELECT docNumber, sprSum, idPredpr
                FROM #inv_prov
                GROUP BY docNumber, sprSum, idPredpr
                HAVING COUNT(*)>1) grp ON main.docNumber=grp.docNumber AND main.sprSum=grp.sprSum AND main.idPredpr=grp.idPredpr
     LEFT JOIN #inv_prov esf ON main.docNumber=esf.docNumber AND main.sprSum=esf.sprSum AND main.idPredpr=esf.idPredpr AND esf.fullName is not NULL
     LEFT JOIN tbPredpr pr ON main.idPredpr=pr.idPredpr 
WHERE main.fullName is NULL
ORDER BY main.docNumber, main.fullName

-- Проводки по НДС без ЭСЧФ
SELECT main.NumBuhSpr, main.docNumber, main.docDate, main.sprSum, pr.namePredpr
FROM #inv_prov main
     INNER JOIN (SELECT docNumber, sprSum, idPredpr
                FROM #inv_prov
                GROUP BY docNumber, sprSum, idPredpr
                HAVING COUNT(*)=1) grp ON main.docNumber=grp.docNumber AND main.sprSum=grp.sprSum AND main.idPredpr=grp.idPredpr
     LEFT JOIN tbPredpr pr ON main.idPredpr=pr.idPredpr 
WHERE main.fullName is NULL
ORDER BY main.docNumber, main.fullName

-- ЭСЧФ без проводки по НДС
SELECT main.fullName, main.invStatus, main.docNumber, main.docDate, main.totalVat, main.totalCost, pr.namePredpr
FROM #inv_prov main
     LEFT JOIN tbPredpr pr ON main.idPredpr=pr.idPredpr 
WHERE main.fullName is not NULL and sprSum is NULL
ORDER BY main.fullName

-- Все кроме дубликатов
SELECT main.*, NULL AS Svplace
FROM #inv_prov main
     INNER JOIN (SELECT docNumber, sprSum, idPredpr
                FROM #inv_prov
                GROUP BY docNumber, sprSum, idPredpr
                HAVING COUNT(*)=1) grp ON main.docNumber=grp.docNumber AND main.sprSum=grp.sprSum AND main.idPredpr=grp.idPredpr
     LEFT JOIN tbPredpr pr ON main.idPredpr=pr.idPredpr 
WHERE main.fullName is NULL

UNION

SELECT main.*, 'С' AS Svplace
FROM #inv_prov main
     LEFT JOIN tbPredpr pr ON main.idPredpr=pr.idPredpr 
WHERE main.fullName is not NULL
ORDER BY main.fullName

/*
SELECT * 
FROM buh_o..tbSpravka
WHERE prizrej=1 and summ=9806.640
*/


